package com.buyuphk.buyupdeskproject;

import java.io.File;
import java.sql.*;
import java.util.Timer;

/**
 * author: JianfeiMa
 * email: majianfei93@163.com
 * created date: 2019-11-25 13:18
 */
public class Main {
    public static void main(String[] args) {
        MySQLiteHelper mySQLiteHelper = null;
        try {
            File dbFolder = new File("./dbs");
            if(!dbFolder.exists()) {
                System.out.println("create dbs directory："+dbFolder.mkdir());
            }
			//Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:dbs/xwf.db");//需要先创建文件夹dbs，数据库文件才会自动创建
            Statement state = conn.createStatement();
            mySQLiteHelper = new MySQLiteHelper(conn);
            //建表
//            state.executeUpdate("drop table if exists books");
            state.executeUpdate("drop table if exists files");
//            state.executeUpdate("create table books(id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(255),author VARCHAR(100))");
            state.executeUpdate("create table files(id INTEGER PRIMARY KEY AUTOINCREMENT,file_name VARCHAR(500))");

//            //插入数据
//            state.executeUpdate("insert into books (name,author) values ('hello','world'),('abc','def'),('jdbc','sqlite')");
//            state.close();
//            state.executeUpdate("insert into files (file_name) values ('511139496726-3.png'),('511139496720'),('511139496721'),('511139496722'),('511139496723'),('511139496724')");
//            state.close();
//
//            //查询
//            PreparedStatement pstate = conn.prepareStatement("select * from books");
//            ResultSet resultSet = pstate.executeQuery();
//            while(resultSet.next()) {
//                int id = resultSet.getInt("id");
//                String name = resultSet.getString("name");
//                String author = resultSet.getString("author");
//                System.out.println("id=" + id + " name=" + name + " author=" + author);
//            }
//            pstate.close();
//
//            //修改
//            PreparedStatement pstate2 = conn.prepareStatement("update books set author = ? where id = ?");
//            pstate2.setString(1, "new author");
//            pstate2.setInt(2, 3);
//            int updateResult = pstate2.executeUpdate();
//            System.out.println("updateResult = " + updateResult);
//            pstate2 = conn.prepareStatement("select * from books where id = 3");
//            ResultSet check = pstate2.executeQuery();
//            while(check.next()) {
//                System.out.println("----id:" + check.getInt(1) + " name:" + check.getString(2) + " author:" + check.getString(3));
//            }
//            pstate2.close();
//
//            //删除
//            PreparedStatement pstate3 = conn.prepareStatement("delete from books where id = ?");
//            pstate3.setInt(1, 3);
//            System.out.println("delete result : " + pstate3.executeUpdate());
//            pstate3.close();
//
//            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        if (mySQLiteHelper != null) {
            /** 定义一秒钟 */
            long oneSecond = 1000L;
            /** 定义一分钟 */
            long oneMinute = 1000L * 60L;
            Timer timer = new Timer();
            MyTimerTask myTimerTask = new MyTimerTask(mySQLiteHelper);
            timer.schedule(myTimerTask, oneSecond, oneMinute);
        }

        while (true) {
            try {
                Thread.sleep(10000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
}
